const express = require('express');
const bodyParser = require('body-parser');
const path = require('path');
const app = express();
app.use(express.static(path.join(__dirname,'public')))
// ----------------------------
var mysql  = require('mysql'); 
const { default: axios } = require('axios');
// function sql (sql) {

// } 
// var Result = []
// 数据库查询函数
 var selectSql = function (sql) {
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : 'root',       
        port: '3306',                   
        database: 'in' 
      }); 
    connection.connect();
    return new Promise((res,req) => {
        connection.query(sql,function (err, result) {
            if(err){
              console.log('[SELECT ERROR] - ',err.message);
              return;
            }
            res(result)
        });
        connection.end();
    })
    
    // console.log("sql",sqlResult);
    // return sqlResult
 }

// --------------------------------
// 数据库插入函数
var addSql = function (sql) {
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : 'root',       
        port: '3306',                   
        database: 'in' 
      }); 
    connection.connect();
    return new Promise((res,reject) => {
        connection.query(sql,function (err, result) {
            if(err){
              console.log('[SELECT ERROR] - ',err.message);
              reject(err.message)
            //   return;
            }
            res(result)
        });
        connection.end();
    })
 }
 //成绩录入
 var scoreSql = function (sql) {
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : 'root',       
        port: '3306',                   
        database: 'in' 
      }); 
    connection.connect();
    return new Promise((res,reject) => {
        connection.query(sql,function (err, result) {
            if(err){
              console.log('[SELECT ERROR] - ',err.message);
              reject(err.message)
            //   return;
            }
            res(result)
        });
        connection.end();
    })
 }
 var addSql = function (sql) {
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : 'root',       
        port: '3306',                   
        database: 'in' 
      }); 
    connection.connect();
    return new Promise((res,reject) => {
        connection.query(sql,function (err, result) {
            if(err){
              console.log('[SELECT ERROR] - ',err.message);
              reject(err.message)
            //   return;
            }
            res(result)
        });
        connection.end();
    })
 }
 //成绩录入
 var scoreSql = function (sql) {
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : 'root',       
        port: '3306',                   
        database: 'in' 
      }); 
    connection.connect();
    return new Promise((res,reject) => {
        connection.query(sql,function (err, result) {
            if(err){
              console.log('[SELECT ERROR] - ',err.message);
              reject(err.message)
            //   return;
            }
            res(result)
        });
        connection.end();
    })
 }
// --------------------------------

app.all('*', function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header('Access-Control-Allow-Headers', 'Content-Type, Content-Length, Authorization, Accept, X-Requested-With , yourHeaderFeild');
    res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS");
    res.header("X-Powered-By",' 3.2.1')
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
});
// -------------------------------------
//用户登录信息获取
app.get('/login',(req,res) => {
    console.log(req.query);
    axios({
        method:'get',
        url:`https://api.weixin.qq.com/sns/jscode2session?appid=wx9248fd116c059231&secret=50f91955d14c2dae85c904726bf2b8c5&js_code=${req.query.code}&grant_type=authorization_code`,        
    }).then((res) => {
        //添加用户
        let sql = `INSERT INTO user VALUES ('${res.data.openid}', '', '', '', '', '', '','','');`
        console.log(sql);
        addSql(sql)
        .then((data) => {
            // res.send(data)
        },(reject) => {
            // res.send(reject)
        })
            console.log(res.data);
        })
    // selectSql('SELECT * FROM user')
    // .then((data) => {
    //     res.send(JSON.stringify(data));
    // })
})

//查询已加入的社团
app.get('/selectUserdepart',(req,res) => {
    console.log(req.query);
    let sql =`select department.departmentName,department.departmentId from department,userdepart where userdepart.userId=${req.query.userId} AND userdepart.departmentId=department.departmentId`
    console.log(sql);
    selectSql(sql)
    .then((data) => {
        res.send(JSON.stringify(data));  
    })
})
//查询社团成员
app.get('/selectDepartuser',(req,res) => {
    console.log('department',req.query);
    let sql =`select user.userName,user.userGrade,user.userProfessional,user.userRule from user,userdepart where userdepart.departmentId=${req.query.departmentId} AND userdepart.userId=user.userId`
    console.log(sql);
    selectSql(sql)
    .then((data) => {
        res.send(JSON.stringify(data));  
    })
})
//查询社长
app.get('/selectDepartuser2',(req,res) => {
    console.log('department',req.query);
    let sql =`select president from department where departmentName='${req.query.departmentName}'`
    console.log(sql);
    selectSql(sql)
    .then((data) => {
        res.send(JSON.stringify(data));  
    })
})
//兴趣探索社团展示查询
app.get('/selectDepartment',(req,res) => {
    console.log(req.query);
    let sql ='select * from department'
    console.log(sql);
    selectSql(sql)
    .then((data) => {
        res.send(JSON.stringify(data));  
    })
})
app.get('/selectDepartment2',(req,res) => {
    console.log(req.query);
    let sql =`select * from department where departmentName='${req.query.name}'`
    console.log(sql);
    selectSql(sql)
    .then((data) => {
        res.send(JSON.stringify(data));  
    })
})
//查询社团管理数
app.get('/selectDepartment3',(req,res) => {
    console.log(req.query);
    let sql =`select * from department where president='${req.query.president}'`
    console.log(sql);
    selectSql(sql)
    .then((data) => {
        res.send(JSON.stringify(data));  
    })
})
//个人信息查询
app.get('/selectMyMessage',(req,res) => {
    console.log(req.query);
    let sql =`select * from user where userId=${req.query.userId}`
    console.log(sql);
    selectSql(sql)
    .then((data) => {
        res.send(JSON.stringify(data));  
    })
})
//消息页面查询
app.get('/selectMessage',(req,res) => {
    console.log(req.query);
    let sql =`select * from notice order by publishTime desc`
    console.log(sql);
    selectSql(sql)
    .then((data) => {
        res.send(JSON.stringify(data));  
    })
})
//首页最新消息查询
app.get('/selectMessage2',(req,res) => {
    console.log(req.query);
    let sql =`select * from notice where order by publishTime desc`
    console.log(sql);
    selectSql(sql)
    .then((data) => {
        res.send(JSON.stringify(data));  
    })
})
//喜欢页面查询
app.get('/selectLove',(req,res) => {
    console.log(req.query);
    let sql =`select * from department where isClick='true'`
    console.log(sql);
    selectSql(sql)
    .then((data) => {
        res.send(JSON.stringify(data));  
    })
})

//发布活动
app.get('/add',(req,res) => {
    console.log(req.query);
    let sql = `INSERT INTO notice VALUES ('003', '${req.query.grade}', '${req.query.name}', '${req.query.title}', '${req.query.time}', '${req.query.detail}', '1901');`
    console.log(sql);
    addSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
//添加成员
app.get('/addUser',(req,res) => {
    console.log(req.query);
    let sql = `INSERT INTO user VALUES ('003', '${req.query.grade}', '${req.query.name}', '${req.query.title}', '${req.query.time}', '${req.query.detail}', '1901');`
    console.log(sql);
    addSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
//添加社团
app.get('/addDepartment',(req,res) => {
    console.log(req.query);
    let sql = `INSERT INTO department VALUES ('${req.query.departmentId}', '${req.query.departmentName}','1', '${req.query.isClick}', '${req.query.president}');`
    console.log(sql);
    addSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
//入团申请
app.get('/apply',(req,res) => {
    console.log(req.query);
    let sql = `INSERT INTO things VALUES ('${req.query.header}', '${req.query.content}', '${req.query.exist}', '${req.query.name}', '${req.query.phone}', '${req.query.qq}', '${req.query.grade}','${req.query.pro}','','${req.query.departmentName}');`
    console.log(sql);
    addSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
//同意申请
app.get('/agree',(req,res) => {
    console.log(req.query);
    let sql = `INSERT INTO things VALUES ('${req.query.header}', '${req.query.content}', '${req.query.exist}', '${req.query.name}', '${req.query.phone}', '${req.query.qq}', '${req.query.grade}','${req.query.pro}');`
    console.log(sql);
    addSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
//同意申请
app.get('/insertAgree',(req,res) => {
    console.log(req.query);
    let sql = `INSERT INTO userdepart VALUES ('${req.query.userId}','${req.query.departmentId}');`
    console.log(sql);
    addSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
//查询社团数量
app.get('/selectDepartmentNumber',(req,res) => {
    console.log(req.query);
    var sql = `select * from department`
    console.log(sql);
    addSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
//查询入团申请1
app.get('/selectApply',(req,res) => {
    console.log(req.query);
    var sql = `select * from things`
    console.log(sql);
    addSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
//查询入团申请2
app.get('/selectApply2',(req,res) => {
    console.log(req.query);
    var sql = `select * from things where name='${req.query.name}'`
    console.log(sql);
    addSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
//查询是否已经加入社团
app.get('/selectJoinDepartment',(req,res) => {
    console.log(req.query);
    var sql = `select * from userdepart where userId='${req.query.userId}' AND departmentId='${req.query.departmentId}'`
    console.log(sql);
    addSql(sql)
    .then((data) => {
        console.log(data);
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})

//修改社团喜欢按钮是否点击
app.get('/updateDepartment',(req,res) => {
    console.log(req.query);
    let sql = `update department set isClick='${req.query.isClick}' where departmentId='${req.query.id}'`
    console.log(sql);
    scoreSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
//修改个人信息
app.get('/updateMyMessage',(req,res) => {
    console.log(req.query);
    let sql = `update user set userName='${req.query.name}',userPhone='${req.query.phone}',userGrade='${req.query.grade}',userQq='${req.query.qq}',userMessage='${req.query.content}',userProfessional='${req.query.pro}' where userId='${req.query.id}'`
    console.log(sql);
    scoreSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
app.get('/delete',(req,res) => {
    let sql = `delete from things where departmentName='${req.query.name}';`
    console.log(sql);
    scoreSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})

app.listen(3000)
console.log('服务器启动成功');